﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;

namespace EarringsRecommendationWeb
{
    public class GlobalFunction
    {
        public static string GetFirstFaceShape(string faceShape)
        {
            string returnVal = string.Empty;

            string[] faceshp= faceShape.Split(',');
            
            returnVal = faceshp[0].ToString();           

            return returnVal;
        }

        public static DataTable GetRemaining(string er_code)
        {
            DataTable dtResult = null;
            if (!string.IsNullOrEmpty(er_code))
            {
                StringBuilder strSql = new StringBuilder();

                strSql.Append("SELECT Er_Code, Er_Remaining FROM Earrings er ");
                strSql.Append(" WHERE er.Er_Code IN (").Append(er_code).Append(")");
                strSql.Append(" AND Er_Remaining <= 0");

                dtResult = SqlClientUtility.ExecuteDataTable(CommandType.Text, strSql.ToString());
            }

            return dtResult;
        }

        public static string[] GetReceiveDate(string er_code)
        {
            string[] returnVal = new string[2];
            if(!string.IsNullOrEmpty(er_code))
            {
                StringBuilder strSql = new StringBuilder();

                strSql.Append("SELECT Max(supp.Orderphase)+ 1 AS [NumofDay]");
                strSql.Append(" , DATEADD(day, Max(supp.Orderphase)+1,CAST(getdate()AS DATE)) AS [ReceiveDate] FROM Earrings er ");
                strSql.Append(" INNER JOIN SupplierOrderphase supp ");
                strSql.Append(" ON er.Er_Style = supp.Config_Id");
                strSql.Append(" WHERE er.Er_Code IN (").Append(er_code).Append(")");

                using (SqlDataReader value = SqlClientUtility.ExecuteReader(CommandType.Text, strSql.ToString()))
                {
                    if (value.Read())
                    {
                        returnVal[0] = SqlClientUtility.GetString(value, "NumofDay", string.Empty);
                        returnVal[1] = SqlClientUtility.GetDateTime(value, "ReceiveDate", DateTime.Now).ToString("dd/MM/yyyy");
                    }
                }
            }

            return returnVal;
        }

        public static string[] GetReceiveDate(List<RecalQty> earringsInBasket)
        {
            List<RecalQty> objEarrings = earringsInBasket;
            string er_code = string.Empty;
            string er_codeTmp = string.Empty;
            string sqlStr = string.Empty;
            string[] returnVal = new string[2];

            foreach (RecalQty item in objEarrings)
            {
                if (!string.IsNullOrEmpty(item.ERCode))
                {
                    sqlStr = string.Empty;
                    sqlStr = "SELECT Er_Remaining FROM Earrings WHERE Er_Code = '" + item.ERCode + "'";

                    object value = SqlClientUtility.ExecuteScalar(CommandType.Text, sqlStr);

                    int remain = Convert.ToInt32(value) - Convert.ToInt32(item.Qty);

                    if (remain <= 0)
                    {
                        if (string.IsNullOrEmpty(er_code))
                        {
                            er_code += "'" + item.ERCode + "'";
                        }
                        else
                        {
                            er_code += ",'" + item.ERCode + "'";
                        }
                    }
                }
            }
            
            StringBuilder strSql = new StringBuilder();
            if (!string.IsNullOrEmpty(er_code))
            {
                strSql.Append("SELECT Max(supp.Orderphase)+ 1 AS [NumofDay]");
                strSql.Append(" , DATEADD(day, Max(supp.Orderphase)+1,CAST(getdate()AS DATE)) AS [ReceiveDate] FROM Earrings er ");
                strSql.Append(" INNER JOIN SupplierOrderphase supp ");
                strSql.Append(" ON er.Er_Style = supp.Config_Id");
                strSql.Append(" WHERE er.Er_Code IN (").Append(er_code).Append(")");

                using (SqlDataReader value = SqlClientUtility.ExecuteReader(CommandType.Text, strSql.ToString()))
                {
                    if (value.Read())
                    {
                        returnVal[0] = SqlClientUtility.GetString(value, "NumofDay", string.Empty);
                        returnVal[1] = SqlClientUtility.GetDateTime(value, "ReceiveDate", DateTime.Now).ToString("dd/MM/yyyy");
                    }
                }
            }
            else
            {
                int day = 1;
                DateTime today = DateTime.Now;
                DateTime receiveDate = today.AddDays(day);

                returnVal[0] = day.ToString();
                returnVal[1] = receiveDate.ToString("dd/MM/yyyy");
            }

            return returnVal;
        }

        public static DataTable GetEarringsInOrder (string all_er_code)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT er.er_code,er.er_name,er.er_detail");
            strSql.Append(" ,er.er_price, ");
            strSql.Append(" 'Data/Earrings/' ").Append(" + erconf.config_name").Append(" + '/' +");
            strSql.Append(" er.er_image_front AS [imgPath],er_remaining,'' AS [er_qty], '' AS [er_status]");
            strSql.Append(" FROM earrings er");
            strSql.Append(" INNER JOIN earringsconfig erconf on er.er_style = erconf.config_id");
            strSql.Append(" WHERE er.er_code IN (").Append(all_er_code).Append(")");

            DataTable dtResult = new DataTable();

            dtResult = SqlClientUtility.ExecuteDataTable(CommandType.Text, strSql.ToString());
            if (dtResult.Rows.Count > 0)
            {
                return dtResult;
            }
            else
            {
                return null;
            }
        }


        public static string UpdateRemaining(List<RecalQty> orderconfirm,string receiveDate,Member mem)
        {
            string er_code = string.Empty;            
            string sqlStr = string.Empty;
            double totalAmount = 0;

            foreach (RecalQty item in orderconfirm)
            {
                SqlParameter[] sqlParams = new SqlParameter[] 
                {
                    new SqlParameter("@Er_Code", item.ERCode),
                    new SqlParameter("@Qty", item.Qty)                    
                };

                //Update Er_Remaining In Earrings
                sqlStr = "UPDATE Earrings SET Er_Remaining = Er_Remaining - @Qty  FROM Earrings WHERE Er_Code = @Er_Code";
                SqlClientUtility.ExecuteNonQuery(CommandType.Text, sqlStr, sqlParams);

                SqlParameter[] sqlParamsPr = new SqlParameter[] 
                {
                    new SqlParameter("@Er_Code", item.ERCode),
                    new SqlParameter("@Qty", item.Qty)                    
                };

                //Get Price for ER_Code
                sqlStr = "SELECT ER_Price * @Qty FROM Earrings WHERE Er_Code = @Er_Code";
                object value = SqlClientUtility.ExecuteScalar(CommandType.Text, sqlStr, sqlParamsPr);

                totalAmount += Convert.ToDouble(value);
            }

            // Insert Order in Reserve and Reserve Detail
           int orderid = InsertReserve(orderconfirm,totalAmount,mem,receiveDate);

           return orderid.ToString();
        }

        public static int InsertReserve(List<RecalQty> orderconfirm,double amount,Member mem,string receiveDate)
        {
            StringBuilder sqlStr = new StringBuilder();
            SqlParameter[] sqlParams = new SqlParameter[] 
            {
                new SqlParameter("@Email", mem.Email),
                new SqlParameter("@TotalAmount", amount) ,
                new SqlParameter("@Status","WaitforReceive"),
                new SqlParameter("@ReceiveDate", Convert.ToDateTime(receiveDate))
            };

            sqlStr.Append("INSERT INTO Reserve (Email,Reserve_Status,TotalAmount,Receive_Date)");
            sqlStr.Append(" VALUES (@Email,@Status,@TotalAmount,@ReceiveDate) ");
            sqlStr.Append(";SELECT @@IDENTITY");

            //int? identity ;
            //identity = (int) 
            object value = SqlClientUtility.ExecuteScalar(CommandType.Text, sqlStr.ToString(), sqlParams);

            if (value != null)
            {
                string strSql = string.Empty;
                foreach (RecalQty item in orderconfirm)
                {
                    SqlParameter[] sqlParamsDet = new SqlParameter[] 
                    {
                        new SqlParameter("@Reserve_Id", Convert.ToInt32(value)),
                        new SqlParameter("@Er_Code", item.ERCode),
                        new SqlParameter("@Qty", item.Qty)                    
                    };

                    strSql = "INSERT INTO ReserveDetail (Reserve_Id,ER_Code,Quatity) VALUES (@Reserve_Id,@Er_Code,@Qty)";
                    SqlClientUtility.ExecuteNonQuery(CommandType.Text, strSql, sqlParamsDet);
                }
            }

            return Convert.ToInt32(value);
        }

        public static void InsertMemberEarrings(MemberEarrings memEarrings,string email)
        {
            SqlParameter[] sqlParams = new SqlParameter[] 
            {
                new SqlParameter("@PersonId", memEarrings.PersonalId),
                new SqlParameter("@FASHP", memEarrings.FaceShape),
                new SqlParameter("@NECKS", memEarrings.Neck),                    
                new SqlParameter("@EmailS", email),
                new SqlParameter("@PointColl", memEarrings.FacePosition),
                new SqlParameter("@FaceImg", memEarrings.PersonalId + ".jpg"),
                new SqlParameter("@LEarPos", memEarrings.LEarlobePosition),
                new SqlParameter("@REarPos", memEarrings.REarlobePosition),
                new SqlParameter("@CDWidth", memEarrings.CDWidth),
                new SqlParameter("@JawLineShape",memEarrings.JawlineShape)
            };

            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO MemEarrings ");
            strSql.Append(" (Person_ID, Email,FaceShape, Neck,");
            strSql.Append(" FacePosition, FaceImage,");
            strSql.Append(" LEarlobePosition,REarlobePosition, JawLineShape , CDWidth  ) ");
            strSql.Append(" VALUES ( ");
            strSql.Append(" @PersonId, @EmailS, @FASHP, @NECKS, @PointColl, @FaceImg, @LEarPos, @REarPos, @JawLineShape ,@CDWidth )");
            //strSql.Append(" ; SELECT @@IDENTITY");
            SqlClientUtility.ExecuteNonQuery(CommandType.Text, strSql.ToString(), sqlParams);

            //object value = SqlClientUtility.ExecuteScalar(CommandType.Text, strSql.ToString(), sqlParams);
            //return value.ToString();
        }

        public static void UpdateMemberEarrings(MemberEarrings memEarrings, string email)
        {
            SqlParameter[] sqlParams = new SqlParameter[] 
            {
                new SqlParameter("@PersonId", memEarrings.PersonalId),
                new SqlParameter("@FASHP", memEarrings.FaceShape),
                new SqlParameter("@NECKS", memEarrings.Neck),                    
                new SqlParameter("@Email", email),
                new SqlParameter("@PointColl", memEarrings.FacePosition),
                new SqlParameter("@FaceImg", memEarrings.PersonalId + ".jpg"),
                new SqlParameter("@LEarPos", memEarrings.LEarlobePosition),
                new SqlParameter("@REarPos", memEarrings.REarlobePosition),
                new SqlParameter("@CDWidth", memEarrings.CDWidth),
                new SqlParameter("@JawLineShape",memEarrings.JawlineShape)
            };

            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE MemEarrings ");
            strSql.Append(" SET FaceShape=@FASHP, Neck=@NECKS,");
            strSql.Append(" FacePosition=@PointColl, FaceImage=@FaceImg,");
            strSql.Append(" LEarlobePosition=@LEarPos, REarlobePosition=@REarPos,");
            strSql.Append(" JawLineShape=@JawLineShape, CDWidth=@CDWidth");
            strSql.Append(" WHERE Email=@Email and Person_ID=@PersonId");
            
            SqlClientUtility.ExecuteNonQuery(CommandType.Text, strSql.ToString(), sqlParams);
        }

        public static MemberEarrings GetMemberEarrings(string personalId)
        {
            MemberEarrings memEarrings = null;
            SqlParameter[] sqlParams = new SqlParameter[] 
            {
                new SqlParameter("@PersonId",personalId),
            };

            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT [Person_ID],[Email],[FaceShape],[Neck]");
            strSql.Append(",[JawLineShape],[CDWidth]");
            strSql.Append(",[FacePosition],[Earlobe]");
            strSql.Append(",[REarlobePosition],[LEarlobePosition]");
            strSql.Append(",[EarShape],[SkinColor],[HairStyle],[FaceImage]");
            strSql.Append(" FROM MemEarrings");
            strSql.Append(" WHERE Person_ID = @PersonId");
            using (SqlDataReader value = SqlClientUtility.ExecuteReader(CommandType.Text, strSql.ToString(), sqlParams))
            {
                if (value.Read())
                {
                    memEarrings = new MemberEarrings();
                    memEarrings.PersonalId = SqlClientUtility.GetString(value, "Person_ID", string.Empty);
                    memEarrings.FaceShape = SqlClientUtility.GetString(value, "FaceShape", string.Empty);
                    memEarrings.Neck = SqlClientUtility.GetString(value, "Neck", string.Empty);
                    memEarrings.CDWidth = SqlClientUtility.GetString(value, "CDWidth", string.Empty);
                    memEarrings.JawlineShape = SqlClientUtility.GetString(value, "JawLineShape", string.Empty);
                    memEarrings.JawlineShape = SqlClientUtility.GetString(value, "CDWidth", string.Empty);                    
                    memEarrings.FacePosition = SqlClientUtility.GetString(value, "FacePosition", string.Empty);
                    memEarrings.Earlobe = SqlClientUtility.GetString(value, "Earlobe", string.Empty);
                    memEarrings.REarlobePosition = SqlClientUtility.GetString(value, "REarlobePosition", string.Empty);
                    memEarrings.LEarlobePosition = SqlClientUtility.GetString(value, "LEarlobePosition", string.Empty);
                    memEarrings.EarShape = SqlClientUtility.GetString(value, "EarShape", string.Empty);
                    memEarrings.SkinColor = SqlClientUtility.GetString(value, "SkinColor", string.Empty);
                    memEarrings.HairStyle = SqlClientUtility.GetString(value, "HairStyle", string.Empty);
                    memEarrings.FaceImage = SqlClientUtility.GetString(value, "FaceImage", string.Empty);                   
                   
                }
            }
            return memEarrings;
        }

        public static void UpdateMemberEarrings(string personalId, string earlobe, string earshape, string hairstyle, string skincolor)
        {
            object nullVal = DBNull.Value;
            SqlParameter[] sqlParams = new SqlParameter[] 
            {
                new SqlParameter("@PersonId", (personalId == null) ? nullVal : personalId),
                new SqlParameter("@Earlobe", (earlobe == null) ? nullVal : earlobe),
                new SqlParameter("@EarShape", (earshape == null) ? nullVal : earshape ),
                new SqlParameter ("@HairStyle", (hairstyle == null) ? nullVal : hairstyle),
                new SqlParameter ("@SkinColor", (skincolor == null) ? nullVal : skincolor)
            };

            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE MemEarrings SET Earlobe=@Earlobe, EarShape=@EarShape");
            strSql.Append(" , HairStyle=@HairStyle, SkinColor=@SkinColor");
            strSql.Append(" WHERE Person_ID = @PersonId");

            

            SqlClientUtility.ExecuteNonQuery(CommandType.Text, strSql.ToString(), sqlParams);

            //MemberEarrings memEarrings = GetMemberEarrings(personalId);
            //return memEarrings;
        }
    }
}